﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI.WebControls;

namespace database
{
    public class DataBase
    {
        private static string ConnectionString = "Data Source=.\\SQLEXPRESS;Initial Catalog=GIS_NEW;Persist Security Info=True;User ID=sa;Password=gis00";
        private SqlConnection connection;
        public string getConnectionstr()
        {
            return ConnectionString;
        }
        protected void OpenConn()
        {
            connection = new SqlConnection(ConnectionString);
            if (connection.State.ToString().ToLower() == "open")
            {
                //连接为打开时 
            }
            else
            {
                //连接为关闭时 
                connection.Open();
            }
        }
        protected void CloseConn()
        {
            if (connection.State.ToString().ToLower() == "open")
            {
                //连接为打开时 
                connection.Close();
                connection.Dispose();
            }
        }
        public DataTable ReadTable(string strSql)
        {
            OpenConn();
            DataTable dd = new DataTable();//创建一个数据表dd
            SqlDataAdapter adapter = new SqlDataAdapter(strSql, connection);//定义并初始化数据适配器
            adapter.Fill(dd);
            CloseConn();
            return dd;
        }
        public DataSet GetDataSet(string strsql)
        {
            OpenConn();
            DataSet ds = new DataSet();
            SqlDataAdapter adapter = new SqlDataAdapter(strsql, connection);
            adapter.Fill(ds);
            CloseConn();
            return ds;
        }

        public int ExecuteNonQuery(string sql, params SqlParameter[] parameters)//通用增删改方法可变参数
        {
            OpenConn();
            SqlCommand cmd = connection.CreateCommand();
            cmd.CommandText = sql;
            foreach (SqlParameter parameter in parameters)
            {
                cmd.Parameters.Add(parameter);
            }
            int count = cmd.ExecuteNonQuery();
            CloseConn();
            return count;
        }
        public object ExecuteScalar(string sql, params SqlParameter[] parameters)//返回首行首列可变参数
        {
            OpenConn();
            SqlCommand cmd = connection.CreateCommand();
            cmd.CommandText = sql;
            foreach (SqlParameter parameter in parameters)
            {
                cmd.Parameters.Add(parameter);
            }
            object obj = cmd.ExecuteScalar();
            CloseConn();
            return obj;
        }
        public DataTable getDataSet(string safeSql, params SqlParameter[] parameters)//获取DataSet
        {
            OpenConn();
            DataSet ds = new DataSet();
            SqlCommand cmd = new SqlCommand(safeSql, connection);
            foreach (SqlParameter parameter in parameters)
            {
                cmd.Parameters.Add(parameter);
            }
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            CloseConn();
            return ds.Tables[0];
        }
        public SqlDataReader ExecuteReader(string sql, params SqlParameter[] parameters)//获取SqlDataReader
        {
            OpenConn();
            SqlCommand cmd = connection.CreateCommand();
            cmd.CommandText = sql;
            try
            {
                foreach (SqlParameter parameter in parameters)
                {
                    cmd.Parameters.Add(parameter);
                }
                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return reader;
            }
            catch
            {
                CloseConn();
                throw;
            }
        }
    }
}